<?php

/* If no headers was sent: main call */
if (!headers_sent())
{
    $excel = True;

    require('database.php');
    require('auth.php');
    require('core.php');

    // Verifica se esta autenticado com pelo menos nivel 3
    validaPermissao(3);

    $location_id = (isset($_GET['location'])? $_GET['location'] : 0);
    $year = (isset($_GET['year'])? $_GET['year'] : date("Y"));
    $month = (isset($_GET['month'])? $_GET['month'] : 1);

    if ($location_id > 0)
    {
        // Pega nome da locacao especifica
        $location_name = query_fetch("SELECT contacts.name FROM locations, contacts " .
            "WHERE locations.locationcontact=contacts.id AND locations.operating=TRUE " .
            "AND locations.id={$location_id} LIMIT 1");
    }

    // Define file type
    header("Content-type: application/vnd.ms-excel");

    // Define file name and to be downloaded
    header("Content-Disposition: attachment; filename=\"Every Location " . 
        ($month == 0? "" : "{$MONTH_NAMES[$month-1]}-") .  "{$year}.xls\"");
    
}

/* Initialize totals */
$TOTAL_CARS = 0;
$TOTAL_LABOR = 0.0;
$TOTAL_LABOR_EXP = 0.0;
$TOTAL_INCOME = 0.0;
$TOTAL_BALANCE = 0.0;
$TOTAL_INC = Array();
foreach($INC_CATEGORIES as $key => $value)
    $TOTAL_INC[$key] = 0.0;
$TOTAL_EXP = Array();
foreach($EXP_CATEGORIES as $key => $value)
    $TOTAL_EXP[$key] = 0.0;

// Mostra tabela
echo "<table id=statistics>";

if (!isset($excel))
{
    echo "<caption>Statistics for Every Location in ",
        ($month == 0? "" : "{$MONTH_NAMES[$month-1]}, "), "{$year}</caption>";

    /* Chart data and label */
    $chart_pos = array();
    $chart_neg = array();
}

echo "<tr>";
echo "<th>Location</th>";
echo "<th>Cars</th>";
echo "<th>Labor(h)</th>";
echo "<th>Labor($)</th>";
echo "<th>Total Income</th>";
foreach($INC_CATEGORIES as $value)
    echo "<th>{$value}</th>";
foreach($EXP_CATEGORIES as $value)
    echo "<th>{$value}</th>";
echo "<th>Balance</th>";
echo "</tr>";

/* Find out the number of days in month */
foreach ($locacoes as $loc)
{
    flush();
    echo "<tr>";
    echo "<td>{$loc['name']}</td>";
    
    /*    
        NOTE: If you accumulate data from reports in a query which finds every
        cars_paid for reports you will probably accumulate reports twice as
        there is a N to one relation. So, report data gattering must be in a
        separate query
    */
    // Report data gattering
    $labor = query_fetch("SELECT " .
                              "SUM(TIME_TO_SEC(labor)) AS labor, " .
                              "-SUM(labor_expense) AS expense " .
                              "FROM reports " .
                              "WHERE state = " . ACCEPTED .
                              " AND special_event=FALSE " . 
                              "AND location_event_id={$loc['id']} " .
                              "AND YEAR(reportdate) = {$year}" .
                              ($month > 0? " AND MONTH(reportdate)={$month}" : ""));
    // cars_paid data gattering
    $cars = query_fetch("SELECT " .
                              "SUM(cars_paid.cars) as cars, SUM(cars_paid.charge*cars_paid.cars) as charge,  " .
                              "SUM(cars_paid.cars*reports.insurance_tax) as insurance " .
                              "FROM cars_paid, reports " .
                              "WHERE cars_paid.report_id=reports.id " .
                              "AND reports.state = " . ACCEPTED .
                              " AND reports.special_event=FALSE " . 
                              "AND reports.location_event_id={$loc['id']} " .
                              "AND YEAR(reports.reportdate) = {$year}" .
                              ($month > 0? " AND MONTH(reports.reportdate)={$month}" : ""));
    $incomes = Array();
    foreach($INC_CATEGORIES as $key => $value)
    {
        $incomes[$key] = query_fetch("SELECT " .
                                  "SUM(value) AS income " .
                                  "FROM duedates " .
                                  "WHERE value > 0 " .
                                  "AND category = {$key} " . 
                                  "AND references_to=" . LOCATION . 
                                  " AND reference_id={$loc['id']} " .
                                  "AND YEAR(payday) = {$year}" .
                                  ($month > 0? " AND MONTH(payday)={$month}" : ""));
    }
    $expenses = Array();
    foreach($EXP_CATEGORIES as $key => $value)
    {
        $expenses[$key] = query_fetch("SELECT " .
                                  "SUM(value) AS expense " .
                                  "FROM duedates " .
                                  "WHERE value < 0 " .
                                  "AND category = {$key} " .
                                  "AND references_to=" . LOCATION . 
                                  " AND reference_id={$loc['id']} " .
                                  "AND YEAR(payday) = {$year}" .
                                  ($month > 0? " AND MONTH(payday)={$month}" : ""));
    }

    /* Print data */
    echo "<td>", ($cars['cars']? number_format($cars['cars']) : ZERO), "</td>";

    echo "<td>", toTime($labor['labor']), "</td>";
    echo "<td>", show_money($labor['expense']), "</td>";
    echo "<td>", show_money($cars['charge'] + $cars['insurance']), "</td>";

    $net_balance = $labor['expense'] + $cars['charge'] + $cars['insurance'];
    foreach($incomes as $key => $value)
    {
        echo "<td>", show_money($value['income']), "</td>";
        $net_balance += $value['income'];
        $TOTAL_INC[$key] += $value['income'];
    }
    foreach($expenses as $key => $value)
    {
        echo "<td>", show_money($value['expense']), "</td>";
        $net_balance += $value['expense'];
        $TOTAL_EXP[$key] += $value['expense'];
    }

    echo "<td>", show_money($net_balance), "</td>";

    echo "</tr>";

    /* Add to totals */
    $TOTAL_CARS += $cars['cars'];
    $TOTAL_LABOR += $labor['labor'];
    $TOTAL_LABOR_EXP += $labor['expense'];
    $TOTAL_INCOME = $cars['charge'] + $cars['insurance']; 
    $TOTAL_BALANCE += $net_balance;

    /* Chart data and label */
    if (!isset($excel))
    {        
        if ($net_balance > 0)
            $chart_pos[urlencode($loc['name'])] = $net_balance;
        else if ($net_balance < 0)
            $chart_neg[urlencode($loc['name'])] = -$net_balance;
    }
}
echo "<tr>";
echo "<th>Total</th>";
echo "<th>", ($TOTAL_CARS > 0? number_format($TOTAL_CARS) : ZERO), "</th>";
echo "<th>", toTime($TOTAL_LABOR), "</th>";
echo "<th>", show_money($TOTAL_LABOR_EXP), "</th>";
echo "<th>", show_money($TOTAL_INCOME), "</th>";
foreach($TOTAL_INC as $value)
    echo "<th>", show_money($value). "</th>";
foreach($TOTAL_EXP as $value)
    echo "<th>", show_money($value). "</th>";
echo "<th>", show_money($TOTAL_BALANCE), "</th>";
echo "</tr>";

echo "</table>";
?>
